''' 滴滴打灰后端应用服务 数据库操作API '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB 
import db_config
import math
from didiutils import Encrypt,generate_verification_code
from tokenutil import TokenUtil

logger = logging.getLogger('didiappservice')
logging.basicConfig(filename='database.txt',filemode='a',level=logging.INFO,
                    format='%(asctime)s - %(name)s - %(levelname)s: %(message)s')

# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=db_config.mysqlInfo['host'],port=db_config.mysqlInfo['port'],
        user=db_config.mysqlInfo['user'],password=db_config.mysqlInfo['password'],database=db_config.mysqlInfo['database'],charset=db_config.mysqlInfo['charset'],cursorclass=pymysql.cursors.DictCursor)
    return pool.connection()


# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,
                  host=db_config.mysqlInfo['host'],
                  port=db_config.mysqlInfo['port'],
                  user=db_config.mysqlInfo['user'],
                  password=db_config.mysqlInfo['password'],
                  database=db_config.mysqlInfo['database'],
                  charset=db_config.mysqlInfo['charset'],
                  cursorclass=pymysql.cursors.DictCursor) # 以字典的形式给出查询结果
    return pool.connection()


'''
admin 登录页面(组)
'''
# 账号密码登录
def login(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    rowdict = {}
    try:
        sql = " select password, salt, is_admin, status,user_id,user_name From user Where user_id=%s "
        cur.execute(sql, (param['user_id'],))
        for row in cur.fetchall():
            rowdict['password'] = row['password']
            rowdict['salt'] = row['salt']
            rowdict['is_admin'] = row['is_admin']
            rowdict['status'] = row['status']
            rowdict['userid'] = row['user_id']
            rowdict['username'] = row['user_name']
        if rowdict:
            if rowdict['salt'] is not None:
                enc = Encrypt(rowdict['salt'])
                j_pwd = enc.decrypt(rowdict['password'])
                if j_pwd != param['password']:
                    result['message'] = '密码错误'
                    result['code'] = 0
                    result['result'] = 'ng'
                else:
                    payload = TokenUtil.getPayload(param['user_id'])
                    token = TokenUtil.encode(payload)
                    rowdict['token'] = token
                    result['code'] = 200
                    result['result'] = rowdict
                    result['message'] = 'ok'
            else:
                result['message'] = '账号错误'
                result['code'] = 0
                result['result'] = 'ng'
            if rowdict['status'] == '1':
                result['message'] = '该用户已禁用，请联系管理员'
                result['code'] = 0
                result['result'] = 'ng'
        else:
            result['message'] = '账号不存在'
            result['code'] = 0
            result['result'] = 'ng'
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result



'''
admin系统用户管理页面（组)
'''
# 获取用户信息列表
def getuserlist(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    reclist = []
    pcount = 0
    try:
        if param:
            sql = r" select password, salt, is_admin, status, user_id, user_name, id, DATE_FORMAT(update_time, '%Y-%m-%d %H:%i:%s') AS update_time, phone, email from user where 1=1 "
            if 'name' in param.keys():
                if param['name'] is not None and param['name'] != '':
                    sql += " and user_name like '%" + param['name'] + "%' "
            if 'selectRole' in param.keys():
                if param['selectRole'] is not None and param['selectRole'] != '':
                    sql += " and is_admin = '" + param['selectRole'] + "' "
            if 'selectStatus' in param.keys():
                if param['selectStatus'] is not None and param['selectStatus'] != '':
                    sql += " and status = '" + param['selectStatus'] + "' "
            sql += " order by update_time desc "
        else:
            sql = "select password, salt, is_admin, status, user_id, user_name, id, DATE_FORMAT(update_time, '%Y-%m-%d %H:%i:%s') AS update_time, phone, email from user order by update_time desc "

        cur.execute(sql)
        for row in cur.fetchall():
            rowdict = {}
            j_pwd = ''
            try:
                enc = Encrypt(row['salt'])
                j_pwd = enc.decrypt(row['password'])
            except:
                pass
            rowdict['password'] = j_pwd
            rowdict['salt'] = row['salt']
            rowdict['role'] = row['is_admin']
            rowdict['status'] = row['status']
            rowdict['userid'] = row['user_id']
            rowdict['username'] = row['user_name']
            rowdict['id'] = row['id']
            rowdict['updatetime'] = row['update_time']
            rowdict['phone'] = row['phone']
            rowdict['email'] = row['email']
            reclist.append(rowdict)
            pcount += 1
        data = {}
        data['list'] = reclist
        data['itemCount'] = 48
        data['page'] = 1
        data['pageSize'] = 10
        data['pageCount'] = math.ceil(pcount / 10)
        result['code'] = 200
        result['result'] = data
        result['message'] = 'ok'

    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 创建用户
def createuser(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    rowdict = {}
    try:
        sql = " select user_id from user where user_id = %s "
        cur.execute(sql, (param['userid']))
        for row in cur.fetchall():
            result['message'] = '登录用户ID已存在'
            result['code'] = 0
            result['result'] = 'ng'
            return result
        salt = generate_verification_code(16)
        enc = Encrypt(salt)
        pwd = enc.encrypt(param['password'])
        sql = " insert into user(user_id,user_name,password,salt,is_admin,phone,email,status,update_time) value(%s,%s,%s,%s,%s,%s,%s,%s,%s) "
        cur.execute(sql, (
        param['userid'], param['username'], pwd, salt, param['role'], param['phone'], param['email'], param['status'],
        datetime.datetime.now()))

        result['code'] = 200
        result['result'] = 'ok'
        result['message'] = ''
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 更新用户
def updateuser(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    rowdict = {}
    try:
        salt = generate_verification_code(16)
        enc = Encrypt(salt)
        pwd = enc.encrypt(param['password'])
        sql = " update user set password=%s,salt=%s,is_admin=%s,phone=%s,email=%s,status=%s,update_time=%s where id = %s "
        cur.execute(sql, (
        pwd, salt, param['role'], param['phone'], param['email'], param['status'], datetime.datetime.now(),
        param['id']))

        result['code'] = 200
        result['result'] = 'ok'
        result['message'] = ''
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 删除用户
def deleteuser(params):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = " delete from user where id = %s "
        cur.execute(sql, (params['id']))
        result['code'] = 200
        result['result'] = 'ok'
        result['message'] = ''
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result


'''
用户档案页面（组） start
'''
'''用户列表 start  Finished by cdx'''
#获取user信息数据
def get_emploee_data(param):
    conn = connect_db()
    cur = conn.cursor()
    pageSize = param['pageSize']
    pageCurrent = param['pageCurrent']
    searchValue = param['searchValue']
    selectProjectId = param['selectProjectId']
    selectGroupId = param['selectGroupId']
    result = {}
    emploee_list = []
    try:
        #构建长字符串常用的方式，这不是元组！
        base_sql = ("SELECT employee.id AS userId,employee.name AS userName,projects.name AS projectName,project_groups.group_name AS groupName, employee.photo AS photo "
                    "FROM employee JOIN projects ON employee.project_id = projects.id JOIN project_groups ON employee.group_id = project_groups.group_id"
        )
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("employee.name LIKE %s")
            sql_params.append(f"%{searchValue}%") # 进行模糊匹配，%是LIKE子句的一部分
        if selectProjectId:
            sql_conditions.append("projects.id = %s")
            sql_params.append(selectProjectId)
        if selectGroupId:
            sql_conditions.append("project_groups.group_id = %s")
            sql_params.append(selectGroupId)
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}" #' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            cur.execute(sql, sql_params)
        else:
            cur.execute(base_sql)

        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['userId'] = row['userId']
            row_dict['userName'] = row['userName']
            row_dict['projectName'] = row['projectName']
            row_dict['groupName'] = row['groupName']
            row_dict['avatar'] = row['photo']
            emploee_list.append(row_dict)

        # 构造返回的result['result']
        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': emploee_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = 'ok'
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#获取选择项目下拉框选项
def get_projectOptions():
    conn = connect_db()
    cur = conn.cursor()
    projcetOptions = []
    result = {}
    try:
        sql = "SELECT name, id FROM projects ORDER BY id"
        cur.execute(sql)
        for row in cur.fetchall():
            projcetOption_dict = {}
            projcetOption_dict["label"] = row['name']
            projcetOption_dict["value"] = str(row['id']) #ptions的value标签要求是字符串类型，数字可不行
            projcetOptions.append(projcetOption_dict)
        result['result'] = projcetOptions
        result['code'] = 200
        result['message'] = 'ok'
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '获取项目下拉框失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(f"projcetOptions:{len(projcetOptions)}")
        return result
#get_projectOptions()

#获取选择班组下拉框选项
def get_groupOptions(selectProjectId):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    groupOptions = []
    try:
        sql = ("SELECT project_groups.group_name AS groupName, project_groups.group_id AS groupId FROM groups_in_project JOIN projects ON groups_in_project.project_id = projects.id JOIN project_groups ON groups_in_project.group_id = project_groups.group_id WHERE projects.id = %s")
        cur.execute(sql, [selectProjectId])
        for row in cur.fetchall():
            projcetOption_dict = {}
            projcetOption_dict["label"] = row['groupName']
            projcetOption_dict["value"] = str(row['groupId'])
            groupOptions.append(projcetOption_dict)
        result['result'] = groupOptions
        result['code'] = 200
        result['message'] = 'ok'
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
# get_groupOptions(1)

#添加employee信息数据
def add_employee(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'username': 'name',
        'idCard': 'idCard',
        'project': 'project_id',
        'group': 'group_id',
        'role': 'role',
        'photo': 'photo',
    }
    idCard = params.get('idCard')
    project_id = params.get('project')
    result = {}
    try:
        sql = ("SELECT * FROM employee WHERE employee.idCard = %s AND employee.project_id = %s")
        cur.execute(sql, [idCard, project_id])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'身份证号为{idCard}的用户已经在项目中'
            return result

        insert_fields = []
        placeholders = []
        insert_values = []
        for key, value in params.items():
            if value is not None:
                insert_fields.append(field_mapping[key])
                placeholders.append('%s')
                insert_values.append(value)
        sql = f"INSERT INTO employee({', '.join(insert_fields)}) VALUES({', '.join(placeholders)})"
        cur.execute(sql, insert_values)

        result['code'] = 200
        result['message'] = '新增用户成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#employee编辑页面初始化数据
def edit_employee_init(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    id = params['employeeID']
    result = {}
    try:
        sql = (f"SELECT employee.*, projects.name AS project_name, project_groups.group_name "
               f"FROM employee JOIN projects on employee.project_id = projects.id JOIN project_groups ON employee.group_id = project_groups.group_id "
               f"WHERE employee.id = %s")
        cur.execute(sql,[id])
        row = cur.fetchone()
        result['result'] = row
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '编辑页面初始化失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#更新emploee信息数据
def update_employee(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'username': 'name',
        'idCard': 'idCard',
        'project': 'project_id',
        'group': 'group_id',
        'role': 'role',
        'photo': 'photo',
        'employee_id': 'id'
    }
    result = {}
    try:
        # 前端返回的project和group可能是名也可能是id，现将其统一为id
        sql = ("SELECT p.projectId, g.groupId "
               "FROM (SELECT id AS projectId FROM projects WHERE %s in (id,name)) AS p "
               "CROSS JOIN "
               "(SELECT group_id AS groupId FROM project_groups WHERE %s in (group_id,group_name)) AS g")
        cur.execute(sql,[params['project'], params['group']])
        fetchone = cur.fetchone()
        params['project'] = fetchone['projectId']
        params['group'] = fetchone['groupId']

        update_fields = []
        update_values = []
        for key, value in params.items():
            if value is not None:
                update_fields.append(f"{field_mapping[key]} = %s")
                update_values.append(value)
        sql = f"UPDATE employee SET {', '.join(update_fields)} WHERE employee.id = %s"
        update_values.append(params['employee_id'])
        cur.execute(sql,update_values)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '更新信息失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#删除employee信息数据（项目中的）
def del_project_employee(params: dict[str: list]):
    conn = connect_db()
    cur = conn.cursor()
    employeeId_list = params.get('delUserList', '')
    result = {}
    try:
        placeholders = ', '.join(['%s']*len(employeeId_list))
        sql = (f"DELETE FROM employee WHERE id in ({placeholders})")
        cur.execute(sql, employeeId_list)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'

        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
'''用户列表 end  Finished by cdx'''
'''
用户档案页面（组） end
'''




'''
项目档案页面（组） start
'''
'''项目列表 start Finished by cdx'''
#获取项目信息数据
def get_project_data(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    pageCurrent = int(params['page'])
    pageSize = int(params['pageSize'])
    searchValue = params.get('searchValue','') #不一定存在的参数
    projcet_list = []
    result = {}
    try:
        # 构建长字符串常用的方式，这不是元组！
        base_sql = (r"SELECT id, date, name, address FROM projects")
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("projects.name LIKE %s")
            sql_params.append(f"%{searchValue}%")  # 进行模糊匹配，%是LIKE子句的一部分
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}"  # ' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            cur.execute(sql, sql_params)
        else:
            cur.execute(base_sql)

        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            print(row)
            row_dict['projectId'] = row['id']
            row_dict['projectName'] = row['name']
            row_dict['projectAddress'] = row['address']
            projcet_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': projcet_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = 'ok'
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#删除项目信息数据
def del_project(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    projectId_list = params.get('delProjectList', '')
    result = {}
    try:
        placeholders = ', '.join(['%s'] * len(projectId_list))
        sql = (f"DELETE FROM projects WHERE id in ({placeholders})")
        cur.execute(sql, projectId_list)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'

        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#新建project
def add_project(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'projectName': 'name',
        'projectAddress': 'address',
    }
    projectName = params.get('projectName')
    result = {}
    try:
        sql = ("SELECT * FROM projects WHERE name = %s")
        cur.execute(sql, [projectName])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'项目 {projectName} 已存经'
            return result
        insert_fields = []
        placeholders = []
        insert_values = []
        for key, value in params.items():
            if value is not None:
                insert_fields.append(field_mapping[key])
                placeholders.append('%s')
                insert_values.append(value)
        sql = f"INSERT INTO projects({', '.join(insert_fields)}) VALUES({', '.join(placeholders)})"
        cur.execute(sql, insert_values)

        result['code'] = 200
        result['message'] = '新增用户成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#project编辑页面初始化数据
def edit_project_init(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    id = params['projectID']
    result = {}
    # 前端field映射到后端数据库字段
    field_mapping = {
        'projectId': 'id',
        'projectName': 'name',
        'projectAddress': 'address',
    }
    try:
        sql = (f"SELECT * "
               f"FROM projects "
               f"WHERE id = %s")
        cur.execute(sql,[id])
        row = cur.fetchone()
        projectInfo = {}
        for field, value in field_mapping.items():
            projectInfo[field] = row[value]
        result['result'] = projectInfo
        result['code'] = 200
        result['message'] = 'ok'
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '编辑页面初始化失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#更新project信息数据
def update_project(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'projectName': 'name',
        'projectAddress': 'address',
        'projectId': 'id',
    }
    result = {}
    try:
        sql = ("SELECT * FROM projects WHERE name = %s AND id != %s")
        cur.execute(sql, [params['projectName'],params['projectId']])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = '项目名重复'
            return result
        update_fields = []
        update_values = []
        for key, value in params.items():
            if value is not None:
                update_fields.append(f"{field_mapping[key]} = %s")
                update_values.append(value)
        sql = f"UPDATE projects SET {', '.join(update_fields)} WHERE id = %s"
        update_values.append(params['projectId'])
        cur.execute(sql,update_values)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '更新信息失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
'''项目列表 end Finished by cdx'''
'''
项目档案页面（组） end
'''


'''
设备管理页面组 start
'''
'''设备组页面 start'''
#获取设备组信息数据
def equipment_group(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    pageCurrent = int(params['page'])
    pageSize = int(params['pageSize'])
    searchValue = params.get('searchValue', '')  # 不一定存在的参数
    projectId = params.get('selectProject','') #不一定存在的参数
    projcet_list = []
    result = {}
    try:
        # 构建长字符串常用的方式，这不是元组！
        base_sql = (r"SELECT equipment_group.id AS equipmentGroupId, equipment_group.name AS equipmentGroupName, remark, projects.name AS projectName "
                    r"FROM equipment_group JOIN projects ON equipment_group.project_id=projects.id")
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("projects.name LIKE %s")
            sql_params.append(f"%{searchValue}%")  # 进行模糊匹配，%是LIKE子句的一部分
        if projectId:
            sql_conditions.append("projects.id = %s")
            sql_params.append(projectId)
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}"  # ' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            cur.execute(sql, sql_params)
        else:
            cur.execute(base_sql)

        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['equipmentGroupId'] = row['equipmentGroupId']
            row_dict['equipmentGroupName'] = row['equipmentGroupName']
            row_dict['projectName'] = row['projectName']
            row_dict['remark'] = row['remark']
            projcet_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': projcet_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = 'ok'
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        print(result)
        return result

#删除equipment_group信息数据
def del_equipment_group(params: dict[str: list]):
    conn = connect_db()
    cur = conn.cursor()
    equipmentGroupList = params.get('equipmentGroupList', '')
    result = {}

    try:
        sql = (f"SELECT equipment_group.name "
               f"FROM equipment_group JOIN equipment ON equipment_group.id = equipment.equipment_group_id "
               f"WHERE equipment.equipment_group_id in (%s)")
        cur.execute(sql,equipmentGroupList)
        fetchone = cur.fetchone()
        print(fetchone)
        if fetchone:
            result['result'] = fetchone['name']
            result['code'] = 0
            result['message'] = f"删除失败：请先删除设备组<--{fetchone['name']}-->管理的所有设备"
            return result
        placeholders = ', '.join(['%s']*len(equipmentGroupList))
        sql = (f"DELETE FROM equipment_group WHERE id in ({placeholders})")
        cur.execute(sql, equipmentGroupList)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'
        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#添加设备组
def add_equipmentGroup(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'equipmentGroup': 'name',
        'project': 'project_id',
        'remark': 'remark',
    }
    name = params.get('equipmentGroup')
    project_id = params.get('project')
    result = {}
    try:
        sql = ("SELECT * FROM equipment_group WHERE name = %s AND project_id = %s")
        cur.execute(sql, [name, project_id])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'设备组名重复：<{name}>设备组已经在所选项目中'
            return result

        insert_fields = []
        placeholders = []
        insert_values = []
        for key, value in params.items():
            if key != 'equipmentGroupId':
                print(value)
                insert_fields.append(field_mapping[key])
                placeholders.append('%s')
                insert_values.append(value)
        sql = f"INSERT INTO equipment_group({', '.join(insert_fields)}) VALUES({', '.join(placeholders)})"
        print(sql)
        cur.execute(sql, insert_values)

        result['code'] = 200
        result['message'] = '新增设备组成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#equipmentGroup编辑页面初始化数据
def init_equipmentGroup(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    id = params['equipmentGroupId']
    result = {}
    # 返回给前端的字典
    try:
        sql = (f"SELECT equipment_group.name AS equipmentGroupName, equipment_group.id AS equipmentGroupId, remark, projects.name AS projectName, projects.id AS projectId "
               f"FROM equipment_group JOIN projects ON equipment_group.project_id = projects.id "
               f"WHERE equipment_group.id = %s")
        cur.execute(sql,[id])
        row = cur.fetchone()
        projectInfo = {}
        projectInfo['equipmentGroup'] = row['equipmentGroupName']
        projectInfo['equipmentGroupId'] = row['equipmentGroupId']
        projectInfo['project'] = row['projectName']
        projectInfo['remark'] = row['remark']
        projectInfo['projectId'] = row['projectId']
        result['result'] = projectInfo
        result['code'] = 200
        result['message'] = 'ok'
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '编辑页面初始化失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#更新equipmentGroup信息数据
def update_equipmentGroup(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'equipmentGroupId': 'id',
        'equipmentGroup': 'name',
        'project': 'project_id',
        'remark': 'remark',
    }
    result = {}
    print(params)
    try:
        # 前端返回的可能是项目id，也可能是项目名，在这儿统一为项目名
        project = params.get('project')
        sql = ("SELECT id FROM projects WHERE %s in (id,name)")
        cur.execute(sql,project)
        print(f"参数是：{params}")
        params['project'] = cur.fetchone()['id']
        print(f"参数是：{params}")
        sql = ("SELECT * FROM equipment_group WHERE name = %s AND id != %s AND project_id = %s")
        print(sql)
        cur.execute(sql, [params['equipmentGroup'], params['equipmentGroupId'], params['project']])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = '设备组名重复：设备组名在所选项目中重复'
            return result
        update_fields = []
        update_values = []
        for key, value in params.items():
            update_fields.append(f"{field_mapping[key]} = %s")
            update_values.append(value)
        sql = f"UPDATE equipment_group SET {', '.join(update_fields)} WHERE id = %s"
        print(f"初始sql:{sql}",update_values)
        update_values.append(params['equipmentGroupId'])
        cur.execute(sql,update_values)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '更新信息失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

'''设备组页面 end'''

'''设备列表页面 start'''
#获取设备列表信息数据
def equipment_list(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    pageCurrent = int(params['page'])
    pageSize = int(params['pageSize'])
    searchValue = params.get('searchValue', '')  # 不一定存在的参数
    projectId = params.get('selectProject','') #不一定存在的参数
    groupId = params.get('selectGroup', '')  # 不一定存在的参数
    # type的label-value转换，和前端保持一致
    equipmentType = {
        '0': '入口',
        '1': '出口',
    }
    equipment_list = []
    result = {}
    try:
        # 构建长字符串常用的方式，这不是元组！
        base_sql = (r"SELECT equipment.id AS equipmentId, equipment.name AS equipmentName, is_online, "
                    "equipment.type AS equipmentType, equipment.create_date AS date, "
                    "equipment_group.name AS equipmentGroup, projects.name AS projectName "
                    "FROM equipment "
                    "JOIN equipment_group ON equipment.equipment_group_id = equipment_group.id "
                    "JOIN projects ON equipment.project_id = projects.id")
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("equipment.name LIKE %s")
            sql_params.append(f"%{searchValue}%")  # 进行模糊匹配，%是LIKE子句的一部分
        if projectId:
            sql_conditions.append("projects.id = %s")
            sql_params.append(projectId)
        if groupId:
            sql_conditions.append("equipment_group.id = %s")
            sql_params.append(groupId)
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}"  # ' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            print(sql)
            cur.execute(sql, sql_params)
        else:
            print(base_sql)
            cur.execute(base_sql)
        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['equipmentId'] = row['equipmentId']
            row_dict['equipmentName'] = row['equipmentName']
            row_dict['isOnline'] = row['is_online']
            row_dict['equipmentGroup'] = row['equipmentGroup']
            row_dict['projectName'] = row['projectName']
            row_dict['equipmentType'] = equipmentType[str(row['equipmentType'])] #type的lable-value转换
            row_dict['date'] = row['date'].strftime('%Y-%m-%d')
            equipment_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': equipment_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = 'ok'
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        print(result)
        return result

#获取选择班组下拉框选项
def equipment_groupOptions(selectProjectId: int):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    groupOptions = []
    try:
        sql = ("SELECT equipment_group.name AS groupName, equipment_group.id AS groupId "
               "FROM equipment_group "
               "JOIN projects ON equipment_group.project_id = projects.id "
               "WHERE projects.id = %s")
        cur.execute(sql, [selectProjectId])
        for row in cur.fetchall():
            projcetOption_dict = {}
            projcetOption_dict["label"] = row['groupName']
            projcetOption_dict["value"] = str(row['groupId'])
            groupOptions.append(projcetOption_dict)
        result['result'] = groupOptions
        result['code'] = 200
        result['message'] = 'ok'
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
# get_groupOptions(1)

# 删除设备
def del_equipment(params: dict[str: list]):
    conn = connect_db()
    cur = conn.cursor()
    delEquipmentList = params.get('delEquipmentList', '')
    result = {}

    try:
        placeholders = ', '.join(['%s']*len(delEquipmentList))
        sql = (f"DELETE FROM equipment WHERE id in ({placeholders})")
        print(sql,delEquipmentList)
        cur.execute(sql, delEquipmentList)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'
        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#添加设备
def add_equipment(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'equipmentId': 'id',
        'equipmentName': 'name',
        'project': 'project_id',
        'equipmentGroup': 'equipment_group_id',
        'type': 'type',
    }
    print(params)
    equipmentId = params.get('equipmentId')
    equipmentName = params.get('equipmentName')
    equipmentGroup = params.get('equipmentGroup')
    result = {}
    try:
        # 检查设备号是否已经存在
        sql = ("SELECT * FROM equipment WHERE id = %s")
        cur.execute(sql, [equipmentId])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'设备号重复：<{equipmentId}>设备号已经存在，请删除原设备后再添加'
            return result
        # 检查设备名是否重复，同一个设备组下面，设备名应该是唯一的
        sql = ("SELECT * FROM equipment WHERE equipment_group_id = %s AND name = %s")
        cur.execute(sql,[equipmentGroup,equipmentName])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'设备名重复：<{equipmentName}>设备名已经在所选设备组中存在'
            return result
        # 插入新的数据
        insert_fields = []
        placeholders = []
        insert_values = []
        for key, value in params.items():
            if key != 'equipmentGroupId':
                print(value)
                insert_fields.append(field_mapping[key])
                placeholders.append('%s')
                insert_values.append(value)
        insert_fields.append('create_date')
        placeholders.append('%s')
        insert_values.append(datetime.datetime.now())
        sql = f"INSERT INTO equipment({', '.join(insert_fields)}) VALUES({', '.join(placeholders)})"
        print(sql)
        cur.execute(sql, insert_values)

        result['code'] = 200
        result['message'] = '新增设备成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result


#equipmentList编辑页面初始化数据
def init_equipment(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    id = params['equipmentId']
    result = {}
    print(id)
    # 返回给前端的字典
    try:
        sql = (f"SELECT equipment.name AS equipmentName, equipment.id AS equipmentId, equipment.type AS type, equipment_group.name AS equipmentGroup, projects.name AS projectName "
               f"FROM equipment "
               f"JOIN projects ON equipment.project_id = projects.id "
               f"JOIN equipment_group ON equipment.equipment_group_id = equipment_group.id "
               f"WHERE equipment.id = %s")
        cur.execute(sql,[id])
        row = cur.fetchone()
        projectInfo = {}
        projectInfo['equipmentName'] = row['equipmentName']
        projectInfo['equipmentId'] = str(row['equipmentId'])
        projectInfo['project'] = row['projectName']
        projectInfo['equipmentGroup'] = row['equipmentGroup']
        projectInfo['type'] = str(row['type'])
        result['result'] = projectInfo
        result['code'] = 200
        result['message'] = 'ok'
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '编辑页面初始化失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#更新equipment信息数据
def update_equipment(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'equipmentId': 'id',
        'equipmentName': 'name',
        'project': 'project_id',
        'equipmentGroup': 'equipment_group_id',
        "type": "type",
    }
    result = {}
    print(params)
    try:
        # 前端返回的可能是项目id，也可能是项目名，在这儿统一为项目id(设备组也是一样的)
        sql = ("SELECT p.projectId, g.groupId "
               "FROM (SELECT id AS projectId FROM projects WHERE %s in (id,name)) AS p "
               "CROSS JOIN "
               "(SELECT id AS groupId FROM equipment_group WHERE %s in (id,name)) AS g")
        cur.execute(sql,[params['project'], params['equipmentGroup']])
        fetchone = cur.fetchone()
        params['project'] = fetchone['projectId']
        params['equipmentGroup'] = fetchone['groupId']

        # 判断设备名在同一个设备组下是否重复
        sql = ("SELECT * FROM equipment WHERE name = %s AND id != %s AND equipment_group_id = %s")
        print(sql)
        cur.execute(sql, [params['equipmentName'], params['equipmentId'], params['equipmentGroup']])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = '设备名重复：设备名在所选设备组中重复'
            return result
        update_fields = []
        update_values = []
        for key, value in params.items():
            update_fields.append(f"{field_mapping[key]} = %s")
            update_values.append(value)
        sql = f"UPDATE equipment SET {', '.join(update_fields)} WHERE id = %s"
        print(f"初始sql:{sql}",update_values)
        update_values.append(params['equipmentId'])
        cur.execute(sql,update_values)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '更新信息失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

'''设备列表页面 end'''
'''
设备管理页面组 end
'''


'''考勤信息可视化'''
# 获取考勤统计数据
def getstatisticaldata(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:

        sql = "with checktime as ( " \
              + "   select " \
              + "       a.employee_id,a.device_id,a.indate,a.intime,b.outtime  " \
              + "   from ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate, min(check_in_time) as intime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,indate " \
              + "       ) as a  " \
              + "       inner join ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_out_time, '%Y-%m-%d') as outdate, max(check_out_time) as outtime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,outdate " \
              + "       ) as b  " \
              + "       on a.employee_id = b.employee_id and a.indate = b.outdate " \
              + ")   " \
              + "   select  " \
              + "       p.staffsize,concat(DATE_FORMAT(m_on.inyearmonth, '%d'), '日') as indays,coalesce(m.managercnt,0) as managercnt,coalesce(w.workercnt,0) as workercnt " \
              + "       ,coalesce(m_on.onsitecnt,0) as m_onsitecnt,coalesce(w_on.onsitecnt,0) as w_onsitecnt,coalesce(m.managercnt - m_on.onsitecnt,0) as m_unsitecnt " \
              + "       ,coalesce(w.workercnt - w_on.onsitecnt,0) as w_unsitecnt " \
              + "   from " \
              + "       projects p " \
              + "   left outer join " \
              + "   (  " \
              + "       select  " \
              + "           project_id,count(id) as managercnt " \
              + "       from  " \
              + "           employee " \
              + "       where  " \
              + "           role = '1' " \
              + "       group by project_id " \
              + "   ) m " \
              + "   on  " \
              + "       p.id = m.project_id " \
              + "   left outer join   " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as workercnt " \
              + "       from " \
              + "           employee " \
              + "       where  " \
              + "           role = '0' " \
              + "       group by project_id " \
              + "   ) w " \
              + "   on  " \
              + "       p.id = w.project_id " \
              + "   left outer join " \
              + "   ( " \
              + "       select " \
              + "           u.project_id,a.indate as inyearmonth,count(a.employee_id) as onsitecnt   " \
              + "       from checktime a  " \
              + "       left outer join " \
              + "           employee u " \
              + "       on  " \
              + "           u.id = a.employee_id " \
              + "       and role = '1' " \
              + "       group by u.project_id,inyearmonth " \
              + "    ) m_on " \
              + "    on  " \
              + "       p.id = m_on.project_id " \
              + "    left outer join   " \
              + "    ( " \
              + "        select " \
              + "            u.project_id,a.indate as inyearmonth,count(a.employee_id) as onsitecnt   " \
              + "        from checktime a  " \
              + "        left outer join " \
              + "            employee u " \
              + "        on  " \
              + "            u.id = a.employee_id " \
              + "        and role = '0' " \
              + "        group by u.project_id,inyearmonth " \
              + "     ) w_on " \
              + "     on  " \
              + "         p.id = w_on.project_id " \
              + "     where 1=1" 
        if param['project'] is not None and param['project'] != '':
            sql += " and p.name like '%" + param['project'] + "%' "
        else:
            sql += " and p.id = (select id from projects order by id limit 1) "
        if param['kinmuym'] is not None and param['kinmuym'] != '':
            sql += " and DATE_FORMAT(m_on.inyearmonth, '%Y年%m月') = '" + param['kinmuym'] + "' "
        else:
            sql += " and DATE_FORMAT(m_on.inyearmonth, '%Y-%m') = (select DATE_FORMAT(check_in_time, '%Y-%m') as checktime from employee_check_record order by checktime desc limit 1) "
        sql += " order by indays "
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['staffsize'] = row['staffsize']
            rowdict['indays'] = row['indays']
            rowdict['managercnt'] = row['managercnt']
            rowdict['workercnt'] = row['workercnt']
            rowdict['m_onsitecnt'] = row['m_onsitecnt']
            rowdict['w_onsitecnt'] = row['w_onsitecnt']
            rowdict['m_unsitecnt'] = row['m_unsitecnt']
            rowdict['w_unsitecnt'] = row['w_unsitecnt']
            lisrec.append(rowdict)

        # 获取当前日期
        if param['kinmuym'] is not None and param['kinmuym'] != '':
            now = pd.Timestamp(param['kinmuym'].replace('年','-').replace('月','-') + '01')
        else:
            now = datetime.now()
         
        # 设置当前月的第一天
        start_date = datetime(now.year, now.month, 1)
         
        # 设置下个月的第一天，然后减去一天得到当前月的最后一天
        end_date = (start_date + timedelta(days=31)).replace(day=1) - timedelta(days=1)
         
        # 使用列表推导式获取当前月的所有日期
        all_days = [start_date + timedelta(days=i) for i in range((end_date - start_date).days + 1)]

        out_days = []
        # 打印结果
        for day in all_days:
            out_days.append(day.strftime('%d') + '日')
        
        lm_onsitecnt = [0] * len(out_days)
        lw_onsitecnt = [0] * len(out_days)
        lm_unsitecnt = [0] * len(out_days)
        lw_unsitecnt = [0] * len(out_days)
        for i in range(len(out_days)):
            for rec in lisrec:
                if out_days[i] == rec['indays']:
                    lm_onsitecnt[i] = rec['m_onsitecnt']
                    lw_onsitecnt[i] = rec['w_onsitecnt']
                    lm_unsitecnt[i] = rec['m_unsitecnt']
                    lw_unsitecnt[i] = rec['w_unsitecnt']
                    break
        
        data = {}
        data['lxAxis'] = out_days
        data['lm_onsitecnt'] = lm_onsitecnt
        data['lw_onsitecnt'] = lw_onsitecnt
        data['lm_unsitecnt'] = lm_unsitecnt
        data['lw_unsitecnt'] = lw_unsitecnt
        result['code'] = 200
        result['result'] = data
        result['message'] = 'ok'
        
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取考勤年月
def getkinmuym(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "  select " \
              + "   DATE_FORMAT(a.check_in_time, '%Y年%m月') as lindate,DATE_FORMAT(a.check_in_time, '%Y-%m') as vindate " \
              + "from employee_check_record a " \
              + "inner join  " \
              + "    employee u " \
              + "on " \
              + "   u.id = a.employee_id "
        if param['project'] is not None and param['project'] != '':
            sql += " inner join projects p on p.id = u.project_id and p.name like '%" + param['project'] + "%' "
        else:
            sql += " inner join projects p on p.id = u.project_id and p.id = (select id from projects order by id limit 1) "
        sql += " group by lindate,vindate order by vindate desc "
        logger.info(sql)
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['label'] = row['lindate']
            rowdict['value'] = row['lindate']
            lisrec.append(rowdict)

        result['code'] = 200
        result['result'] = lisrec
        result['message'] = 'ok'
        
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取考勤项目
def getproject():
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = " select id,name from projects group by id,name " 
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['value'] = row['name']
            rowdict['label'] = row['name']
            lisrec.append(rowdict)
        result['code'] = 200
        result['result'] = lisrec
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取当日考勤信息
def getkinmuinfo(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "with checktime as ( " \
              + "   select " \
              + "       a.employee_id,a.device_id,a.indate,a.intime,b.outtime  " \
              + "   from ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate, min(check_in_time) as intime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,indate " \
              + "       ) as a  " \
              + "       inner join ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_out_time, '%Y-%m-%d') as outdate, max(check_out_time) as outtime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,outdate " \
              + "       ) as b  " \
              + "       on a.employee_id = b.employee_id and a.indate = b.outdate " \
              + ")   " \
              + "select  " \
              + "   p.staffsize,coalesce(m.managercnt,0) as managercnt,coalesce(w.workercnt,0) as workercnt " \
              + "   ,coalesce(m_on.onsitecnt,0) as m_onsitecnt,coalesce(w_on.onsitecnt,0) as w_onsitecnt,coalesce(m.managercnt - m_on.onsitecnt,0) as m_unsitecnt " \
              + "   ,coalesce(w.workercnt - w_on.onsitecnt,0) as w_unsitecnt,coalesce(m_nocheckin.m_nocheckincnt,0) as m_nocheckincnt " \
              + "   ,coalesce(w_nocheckin.w_nocheckincnt,0) as w_nocheckincnt " \
              + "from " \
              + "   projects p " \
              + "left outer join   " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as managercnt " \
              + "       from  " \
              + "           employee " \
              + "               where  " \
              + "           role = '1' " \
              + "               group by project_id " \
              + "   ) m " \
              + "on  " \
              + "   p.id = m.project_id " \
              + "left outer join   " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as workercnt " \
              + "       from  " \
              + "           employee " \
              + "               where  " \
              + "           role = '0' " \
              + "               group by project_id " \
              + "   ) w " \
              + "on  " \
              + "   p.id = w.project_id " \
              + "left outer join  " \
              + "   ( " \
              + "       select " \
              + "           u.project_id,count(case  " \
              + "           when (select max(e.check_in_time) from employee_check_record e where e.employee_id = a.employee_id and DATE_FORMAT(e.check_in_time,'%Y-%m-%d') = a.indate) < a.outtime and DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') > DATE_FORMAT(a.outtime,'%Y-%m-%d %H:%i:%s') " \
              + "           then 1 else 0 end) as onsitecnt   " \
              + "       from checktime a  " \
              + "       inner join " \
              + "           employee u  " \
              + "       on  " \
              + "           u.id = a.employee_id " \
              + "       and role = '1' " \
              + "       where DATE_FORMAT(now(),'%Y-%m-%d') = a.indate " \
              + "               group by u.project_id " \
              + "   ) m_on " \
              + "on  " \
              + "   p.id = m_on.project_id " \
              + "left outer join   " \
              + "   ( " \
              + "       select " \
              + "           u.project_id,count(case  " \
              + "           when (select max(e.check_in_time) from employee_check_record e where e.employee_id = a.employee_id and DATE_FORMAT(e.check_in_time,'%Y-%m-%d') = a.indate) < a.outtime and DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') > DATE_FORMAT(a.outtime,'%Y-%m-%d %H:%i:%s') " \
              + "           then 1 else 0 end) as onsitecnt   " \
              + "       from checktime a  " \
              + "       inner join " \
              + "           employee u " \
              + "       on  " \
              + "           u.id = a.employee_id " \
              + "       and role = '0' " \
              + "       where DATE_FORMAT(now(),'%Y-%m-%d') = a.indate " \
              + "       group by u.project_id " \
              + "   ) w_on " \
              + "on  " \
              + "   p.id = w_on.project_id " \
              + "left outer join  " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as m_nocheckincnt " \
              + "       from  " \
              + "           employee " \
              + "       where  " \
              + "           role = '1' and id not in (select employee_id from checktime where DATE_FORMAT(now(),'%Y-%m-%d') = indate group by employee_id) " \
              + "       group by project_id " \
              + "   ) m_nocheckin " \
              + "on  " \
              + "   p.id = m_nocheckin.project_id " \
              + "left outer join  " \
              + "   ( " \
              + "       select  " \
              + "           project_id,count(id) as w_nocheckincnt " \
              + "       from  " \
              + "           employee " \
              + "       where  " \
              + "           role = '0' and id not in (select employee_id from checktime where DATE_FORMAT(now(),'%Y-%m-%d') = indate group by employee_id) " \
              + "       group by project_id " \
              + "   ) w_nocheckin " \
              + "on  " \
              + "   p.id = w_nocheckin.project_id " \
              + "     where 1=1" 
        if param['project'] is not None and param['project'] != '':
            sql += " and p.name like '%" + param['project'] + "%' "
        else:
            sql += " and p.id = (select id from projects order by id limit 1) "

        cur.execute(sql)
        rowdict = {}
        for row in cur.fetchall():
            rowdict['staffsize'] = row['staffsize']
            rowdict['managercnt'] = row['managercnt']
            rowdict['workercnt'] = row['workercnt']
            rowdict['m_onsitecnt'] = row['m_onsitecnt']
            rowdict['w_onsitecnt'] = row['w_onsitecnt']
            rowdict['m_unsitecnt'] = row['m_unsitecnt']
            rowdict['w_unsitecnt'] = row['w_unsitecnt']
            rowdict['m_nocheckincnt'] = row['m_nocheckincnt']
            rowdict['w_nocheckincnt'] = row['w_nocheckincnt']
            rowdict['m_onsitecnt_degree'] = 0
            if rowdict['managercnt'] > 0 and rowdict['m_onsitecnt'] > 0:
                rowdict['m_onsitecnt_degree'] = round(rowdict['m_onsitecnt']/rowdict['managercnt'] * 100, 2)
            rowdict['w_onsitecnt_degree'] = 0
            if rowdict['workercnt'] > 0 and rowdict['w_onsitecnt'] > 0:
                rowdict['w_onsitecnt_degree'] = round(rowdict['w_onsitecnt']/rowdict['workercnt'] * 100, 2)
            rowdict['m_unsitecnt_degree'] = 0
            if rowdict['managercnt'] > 0 and rowdict['m_unsitecnt'] > 0:
                rowdict['m_unsitecnt_degree'] = round(rowdict['m_unsitecnt']/rowdict['managercnt'] * 100, 2)
            rowdict['w_unsitecnt_degree'] = 0
            if rowdict['workercnt'] > 0 and rowdict['w_unsitecnt'] > 0:
                rowdict['w_unsitecnt_degree'] = round(rowdict['w_unsitecnt']/rowdict['workercnt'] * 100, 2)

        result['code'] = 200
        result['result'] = rowdict
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取工作人员考勤数据
def getkinmulist(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    try:
        sql = "with checktime as (  " \
              + "   select " \
              + "       a.employee_id,a.device_id,a.indate,a.intime,b.outtime  " \
              + "   from ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate, min(check_in_time) as intime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,indate " \
              + "       ) as a  " \
              + "       inner join ( " \
              + "       select " \
              + "           employee_id,device_id,DATE_FORMAT(check_out_time, '%Y-%m-%d') as outdate, max(check_out_time) as outtime  " \
              + "               from employee_check_record  " \
              + "       group by employee_id,device_id,outdate " \
              + "       ) as b  " \
              + "       on a.employee_id = b.employee_id and a.indate = b.outdate " \
              + "       where exists (select c.employee_id,c.device_id,DATE_FORMAT(c.check_in_time, '%Y-%m-%d') as indate from employee_check_record c where c.employee_id = a.employee_id and DATE_FORMAT(c.check_in_time, '%Y-%m-%d') = a.indate group by c.employee_id,c.device_id,indate having count(c.employee_id) = 1) " \
              + "       union " \
              + "       select employee_id,device_id,DATE_FORMAT(check_in_time, '%Y-%m-%d') as indate,check_in_time,(select outtime.check_out_time from   " \
              + "       (select " \
              + "           employee_id,device_id,check_out_time " \
              + "               from employee_check_record  " \
              + "       where check_in_time is null " \
              + "       order by check_in_time " \
              + "       ) outtime where outtime.employee_id = intime.employee_id and intime.check_in_time <= outtime.check_out_time limit 1) as check_out_time " \
              + "       from " \
              + "       (select " \
              + "           employee_id,device_id,check_in_time " \
              + "               from employee_check_record  " \
              + "       where check_out_time is null " \
              + "       order by check_in_time " \
              + "       ) intime " \
              + ") " \
              + "select u.name,u.gender,p.name as project_nm,g.group_name as group_nm,case when u.role = '1' then '管理人员' else '工人' end as rolenm, " \
              + "       DATE_FORMAT(c.intime, '%H:%i:%s') as intime,DATE_FORMAT(c.outtime, '%H:%i:%s') as outtime,concat(TIMESTAMPDIFF(HOUR, c.intime, c.outtime),'小时') as worktime," \
              + "       case when (select count(e.employee_id) from employee_check_record e where e.employee_id = c.employee_id and DATE_FORMAT(e.check_in_time, '%Y-%m-%d') = c.indate group by e.employee_id,e.device_id,DATE_FORMAT(e.check_in_time, '%Y-%m-%d')) > 1 then '多次打卡，异常' " \
              + "       when DATE_FORMAT(c.intime, '%H:%m:%s') > '08:30:00' then '迟到' when DATE_FORMAT(c.outtime, '%H:%m:%s') < '18:00:00' then '早退' when c.intime is null then '缺勤' else '打卡正常' end as notes " \
              + "from  " \
              + "   employee u " \
              + "left outer join " \
              + "   projects p " \
              + "on  " \
              + "   u.project_id = p.id " \
              + "left outer join " \
              + "   dididahui.project_groups g " \
              + "on   " \
              + "   u.group_id = g.group_id " \
              + "left outer join  " \
              + "   checktime c " \
              + "on  " \
              + "   u.id = c.employee_id  and  c.indate = DATE_FORMAT(now(),'%Y-%m-%d')"
        if param['kubun'] == '1':
           sql += " where u.role = '1' and u.id in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"       
        if param['kubun'] == '2':
           sql += " where u.role = '1' and u.id not in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"        
        if param['kubun'] == '3':
           sql += " where u.role = '0' and u.id in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"       
        if param['kubun'] == '4':
           sql += " where u.role = '0' and u.id not in (select employee_id from checktime where indate = DATE_FORMAT(now(),'%Y-%m-%d'))"
        logger.info(sql)
        cur.execute(sql)
        lisrec = []
        for row in cur.fetchall():
            rowdict = {}
            rowdict['name'] = row['name']
            rowdict['gender'] = row['gender']
            rowdict['project'] = row['project_nm']
            rowdict['group'] = row['group_nm']
            rowdict['role'] = row['rolenm']
            rowdict['intime'] = row['intime']
            rowdict['outtime'] = row['outtime']
            rowdict['worktime'] = row['worktime']
            rowdict['notes'] = row['notes']
            lisrec.append(rowdict)
        data = {}
        data['list'] = lisrec
        data['itemCount'] = 48
        data['page'] = 1
        data['pageSize'] = 10
        result['code'] = 200
        result['result'] = data
        result['message'] = 'ok'
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

